﻿using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using NFine.Data.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Transactions;

namespace Main.DBHelp
{
   public  class PHPCanshu
    {
        //新增
        /// <summary>
        /// 新增,使用数组放入,因而拆分数组
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string add(string tableName, Dictionary<object, object> obj, bool fetchsql = false)
        {
            if(fetchsql)
            {
                return PHPDBDo.add(tableName, obj);
            }

            Dictionary<object, object> dc = new Dictionary<object, object>();

            string sql = "";
            sql += "INSERT INTO " + tableName.ToString();
            sql += "(";
            foreach (var item in obj)
            {
                sql += item.Key + ",";
            }
            sql = sql.Remove(sql.Length - 1, 1);
            sql += ") values (";
            foreach (var item in obj)
            {
                sql += "?" + item.Key + "a,";
                dc["?" + item.Key + "a"] = item.Value;
            }
            sql = sql.Remove(sql.Length - 1, 1);
            sql += ")";

            if (Docommit(sql, dc))
            {
                return "T";
            }
            else
            {
                return "F";
            }
        }

        /// <summary>
        /// 修改 使用数组放入,因而拆分数组
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string update(string tableName, Dictionary<object, object> obj, Dictionary<object, object> where, bool fetchsql = false)
        {

            if (fetchsql)
            {
                return PHPDBDo.update(tableName, obj, where);
            }

            Dictionary<object, object> dc = new Dictionary<object, object>();

            string sql = "";
            sql += "update " + tableName.ToString() + " set ";
            foreach (var item in obj)
            {
                sql += item.Key + "=?" + item.Key + "u1,";
                dc["?" + item.Key + "u1"] = item.Value;
            }
            sql = sql.Remove(sql.Length - 1, 1);
            sql += " where ";
            foreach (var item in where)
            {
                sql += item.Key + "=?" + item.Key + "u2 and ";
                dc["?" + item.Key + "u2"] = item.Value;
            }
            sql = sql.Remove(sql.Length - 4);

            if (Docommit(sql, dc))
            {
                return "T";
            }
            else
            {
                return "F";
            }
        }

        /// <summary>
        /// 删除 使用数组放入,因而拆分数组
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string delete(string tableName, Dictionary<object, object> where, bool fetchsql = false)
        {
            if (fetchsql)
            {
                return PHPDBDo.delete(tableName, where);
            }

            Dictionary<object, object> dc = new Dictionary<object, object>();

            string sql = "";
            sql += "delete from " + tableName.ToString();
            sql += " where ";
            foreach (var item in where)
            {
                sql += item.Key + "=?" + item.Key + "d and ";
                dc["?" + item.Key + "d"] = item.Value;
            }
            sql = sql.Remove(sql.Length - 4);

            if (Docommit(sql, dc))
            {
                return "T";
            }
            else
            {
                return "F";
            }
        }

        /// <summary>
        /// 按条件全部找出来
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <param name="returnType">0 list,1datable</param>
        /// <param name="fetchsql"></param>
        /// <returns></returns>
        public static string select<T>(string filed ,string tableName, Dictionary<object, object> where, bool fetchsql = false)
        {
            Dictionary<object, object> dc = new Dictionary<object, object>();

            string sql = "select " + filed + " from " + tableName+ " where 1=1 and ";
            string allSql = sql;
            foreach (var item in where)
            {
                sql+= item.Key + "=?" + item.Key + "s and ";
                dc["?" + item.Key + "s"] = item.Value;
                allSql+= item.Key + "=" + item.Value + " and ";
            }
            sql = sql.Remove(sql.Length - 4);
            allSql = allSql.Remove(allSql.Length - 4);

            if(fetchsql)
            {
                return allSql;
            }

            DataTable dt= DbHelper.GetList(sql, dc);
            return DataTableToJsonWithJsonNet(dt);
         }

        public static string DataTableToJsonWithJsonNet(DataTable table)
        {
            string jsonString = string.Empty;
            jsonString = JsonConvert.SerializeObject(table);
            return jsonString;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool Docommit(string sql, Dictionary<object, object> obj)
        {
            using (TransactionScope transaction = new TransactionScope())//使用事务
            {
                try
                {
                    using (MySqlConnection conn = new MySqlConnection(DbHelper.connstring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = conn;

                            foreach (var item in obj)
                            {
                                cmd.Parameters.AddWithValue(item.Key.ToString(), item.Value.ToString());
                            }

                            conn.Open();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    transaction.Complete();//就这句就可以了。
                    return true;
                }
                catch (Exception ex)
                {
                    throw ex.InnerException;

                }
            }
        }

        //新增
        /// <summary>
        /// 新增,使用数组放入,因而拆分数组
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static string ListAdd(string tableName, List<Dictionary<object, object>> obj, bool fetchsql = false)
        {
            if (fetchsql)
            {
                return ListAddSql(tableName, obj);
            }
            string sql = "";
            Dictionary<object, object> dc = new Dictionary<object, object>();
           List< Dictionary<object, object>> Listdc = new List<Dictionary<object, object>>();
            int addCount = 0;
            foreach (var items in obj)
            {
                dc= new Dictionary<object, object>();
                sql += " INSERT INTO " + tableName.ToString();
                sql += "(";
          
                foreach (var item in items)
                {
                    sql += item.Key + ",";
                }
            
                sql = sql.Remove(sql.Length - 1, 1);
                sql += ") values (";
          
                foreach (var item in items)
                {
                    sql += "?" + item.Key + "a"+addCount + ",";
                    dc["?" + item.Key + "a" + addCount] = item.Value;
                }
                Listdc.Add(dc);
                sql = sql.Remove(sql.Length - 1, 1);
                sql += "); ";
                addCount++;
            }
            if (ListDocommit(sql, Listdc))
            {
                return "T";
            }
            else
            {
                return "F";
            }
        }

        public static string ListAddSql(string tableName, List<Dictionary<object, object>> obj)
        {
            string sql = "";
            sql += "INSERT INTO " + tableName.ToString();
            sql += "(";

            foreach (var items in obj)
            {
                foreach (var item in items)
                {
                    sql += item.Key + ",";
                }
            }
            sql = sql.Remove(sql.Length - 1, 1);
            sql += ") values (";

            foreach (var items in obj)
            {
                foreach (var item in items)
                {
                    sql += "'" + item.Value + "',";
                }
            }

            sql = sql.Remove(sql.Length - 1, 1);
            sql += ")";

            return sql;
        }

        /// <summary>
        /// 为liet添加与更新,需要改写
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static bool ListDocommit(string sql, List<Dictionary<object, object>> obj)
        {
            using (TransactionScope transaction = new TransactionScope())//使用事务
            {
                try
                {
                    using (MySqlConnection conn = new MySqlConnection(DbHelper.connstring))
                    {
                        using (MySqlCommand cmd = new MySqlCommand())
                        {
                            cmd.CommandText = sql;
                            cmd.CommandType = CommandType.Text;
                            cmd.Connection = conn;
                            foreach(var items in obj)
                            {
                                foreach (var item in items)
                                {
                                    cmd.Parameters.AddWithValue(item.Key.ToString(), item.Value.ToString());
                                }
                            }

                            conn.Open();
                            cmd.ExecuteNonQuery();
                        }
                    }
                    transaction.Complete();//就这句就可以了。
                    return true;
                }
                catch (Exception ex)
                {
                    throw ex.InnerException;

                }
            }
        }
    }
}
